package com.wstuo.common.file.excel;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PipedInputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelUtil {
	final static Logger LOGGER = Logger.getLogger(ExcelUtil.class);
	private static void createCell(HSSFRow row, HSSFCellStyle cellstyle,
			short col, String val) {
		HSSFCell cell = row.createCell(col);
		cell.setCellStyle(cellstyle);
		HSSFRichTextString cellString = new HSSFRichTextString(val);
		cell.setCellValue(cellString);
	}

	public static List<String[]> readSheet(String sheetName, InputStream is)
			throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook(is);
		HSSFSheet sheet = wb.getSheet(sheetName);
		List<String[]> data = new ArrayList<String[]>();
		int cols = sheet.getRow(0).getPhysicalNumberOfCells();
		for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
			String[] rowData = new String[cols];
			data.add(rowData);
			HSSFRow row = sheet.getRow(j);
			for (int i = 0; i < cols; i++) {

				rowData[i] = row.getCell(i).getRichStringCellValue()
						.getString();
			}
		}
		return data;
	}

	/**
	 * Export sheet
	 */
	public static void createSheet(String sheetName, String[] colNames,
			List<String[]> data, OutputStream os) {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(sheetName);
		HSSFCellStyle cellstyle = wb.createCellStyle();
		cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);

		HSSFRow rowHead = sheet.createRow((short) 0);
		sheet.createFreezePane(0, 1);
		for (int i = 0; i < colNames.length; i++) {
			createCell(rowHead, cellstyle, (short) i, colNames[i]);
		}
		for (int j = 0; j < data.size(); j++) {
			HSSFRow row = sheet.createRow((short) j + 1);
			String[] rowData = data.get(j);
			for (int i = 0; i < rowData.length; i++) {
				createCell(row, cellstyle, (short) i, rowData[i]);
			}
		}
		try {
			if(wb!=null){
				wb.write(os);
			}
			if(os!=null){
				os.flush();
				os.close();
			}
		} catch (Exception e) {
			LOGGER.error(e);
		}
	}

	/**
	 * Export sheet
	 * 
	 * @return InputStream
	 */
	public static InputStream createSheetExcel(String sheetName,
			String[] colNames, List<String[]> data) {
		PipedInputStream pis = null;
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(sheetName);
		HSSFCellStyle cellstyle = wb.createCellStyle();
		cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);

		HSSFRow rowHead = sheet.createRow((short) 0);
		sheet.createFreezePane(0, 1);
		for (int i = 0; i < colNames.length; i++) {
			createCell(rowHead, cellstyle, (short) i, colNames[i]);
		}
		for (int j = 0; j < data.size(); j++) {
			HSSFRow row = sheet.createRow((short) j + 1);
			String[] rowData = data.get(j);
			for (int i = 0; i < rowData.length; i++) {
				createCell(row, cellstyle, (short) i, rowData[i]);
			}
		}
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		try {
			wb.write(os);
			return new ByteArrayInputStream(os.toByteArray());
		} catch (Exception e) {
			LOGGER.error(e);
		}finally{
			try {
				if(os!=null){
					os.flush();
					os.close();
				}
			} catch (IOException e) {
				LOGGER.error(e);
			}
			
		}
		return pis;
	}


}
